create external table jms_dm.dm_system_unfinished_summary_dt
(
    order_source_name           string COMMENT '订单来源名称',
    end_manage_region_code      string COMMENT '末端网点所属管理大区编码',
    end_manage_region_name      string COMMENT '末端网点所属管理大区名称',
    end_agent_code              string COMMENT '末端网点所属代理区编码',
    end_agent_name              string COMMENT '末端网点所属代理区名称',
    end_franchisee_code         string COMMENT '末端网点所属加盟商编码',
    end_franchisee_name         string COMMENT '末端网点所属加盟商名称',
    final_sign_network_code     string COMMENT '末端网点所属加盟商编码',
    final_sign_network_name     string COMMENT '最终签收派件网点名称',
    need_sign_count             bigint COMMENT '应签收',
    finished_count              bigint COMMENT '完结量',
    unfinished_count            bigint COMMENT '未完结量',
    unfinished_third_party      string COMMENT '第三方对接',
    unfinished_deliver_out      string COMMENT '快件取出',
    unfinished_deliver          string COMMENT '收派员派件',
    unfinished_other            string COMMENT '其他',
    final_plan_sign_date        date COMMENT '规划签收日期',
    unfinished_difficult        string COMMENT '问题件',
    unfinished_duty_deliver     string COMMENT '未完结责任网点出仓',
    unfinished_duty_end_network string COMMENT '未完结责任网点到件',
    unfinished_duty_end_center  string COMMENT '未完结责任中心发件',
    end_provider_id             string COMMENT '末端网点所属省份id',
    end_provider_name           string COMMENT '末端网点所属省份名称',
    end_city_id                 string COMMENT '末端网点所属城市id',
    end_city_name               string COMMENT '末端网点所属城市名称',
    end_area_id                 string COMMENT '末端网点所属区县id',
    end_area_name               string COMMENT '末端网点所属区县名称'
)
    comment '系统未完结汇总新'
    PARTITIONED BY ( dt string COMMENT '时间分区')
    stored as parquet
    LOCATION '/dw/hive/jms_dm.db/external/dm_system_unfinished_summary_dt'
    TBLPROPERTIES ( 'discover.partitions' = 'false', 'parquet.column.index.access' = 'true');

create external table jms_tmp.dm_system_unfinished_summary_mid_dt
(
    order_source_name       string comment '订单来源',
    final_sign_network_code string comment '网点',
    final_plan_sign_date    string comment '签收日期',
    unfinished_count_two    int comment '收派员派件和其他',
    unfinished_fine         decimal(16, 1) comment '未完结罚款金额',
    is_shaidan              int comment '是否筛单网点(0:否;1:是)',
    is_delay                int comment '是否顺延网点(0:否;1:是)',
    is_new_open             int comment '是否新开网点(0:否;1:是)',
    is_special_report       int comment '是否特殊(0:否;1:是)'
)
    COMMENT '未完结汇总中间表'
    PARTITIONED BY ( dt STRING COMMENT 't-7')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_tmp.db/external/dm_system_unfinished_summary_mid_dt'
;
